package com.distributeredis.redis_springboot.springpoi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.Date;

public class PoiExport {
    String PATH = "H:\\LUA\\redis_springboot\\src\\main\\java\\com\\distributeredis\\redis_springboot\\springpoi\\";
    // 导出
    @Test
    public void test1() throws Exception {
        FileOutputStream fos = new FileOutputStream(PATH+"export.xlsx");
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("导出");

        // 创建一个字体对象
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 设置字体名称
        font.setFontHeightInPoints((short) 14); // 设置字体大小
        font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色

        /**
         * 设置单元格水平居中，垂直居中
         */
        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFont(font);
        // 第一行
        Row row = sheet.createRow(0);
        // 第一列
        Cell cell = row.createCell(0);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);
        // 第二列
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("班级");
        cell1.setCellStyle(style);

        Cell cell2 = row.createCell(2);
        cell2.setCellValue("学生成绩表");
        cell2.setCellStyle(style);

        Row row1 = sheet.createRow(1);
        Cell cell3 = row1.createCell(2);
        cell3.setCellValue("第一学期");
        cell3.setCellStyle(style);

        Cell cell4 = row1.createCell(4);
        cell4.setCellValue("第二学期");
        cell4.setCellStyle(style);

        Row row2 = sheet.createRow(2);
        Cell cell5 = row2.createCell(2);
        cell5.setCellValue("前半年");
        cell5.setCellStyle(style);

        Cell cell6 = row2.createCell(3);
        cell6.setCellValue("后半年");
        cell6.setCellStyle(style);

        Cell cell7 = row2.createCell(4);
        cell7.setCellValue("前半年");
        cell7.setCellStyle(style);

        Cell cell8 = row2.createCell(5);
        cell8.setCellValue("后半年");
        cell8.setCellStyle(style);


        // 合并单元格
        /**
         *
         *      Creates new cell range. base 0
         *      @param firstRow Index of first row
         *      @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}
         *      @param firstCol Index of first column
         *      @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}
         *     public CellRangeAddress( int firstRow, int lastRow, int firstCol, int lastCol)
         */
        CellRangeAddress cra1 = new CellRangeAddress(0, 2, 0, 0);
        CellRangeAddress cra2 = new CellRangeAddress(0, 2, 1, 1);
        CellRangeAddress cra3 = new CellRangeAddress(0, 0, 2, 5);
        CellRangeAddress cra4 = new CellRangeAddress(1, 1, 2, 3);
        CellRangeAddress cra5 = new CellRangeAddress(1, 1, 4, 5);
        /**
         * CellRangeAddress region  = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
         * sheet.addMergedRegion(region)
         */
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
        sheet.addMergedRegion(cra4);
        sheet.addMergedRegion(cra5);

        workbook.write(fos);
        fos.close();
    }
    @Test
    public void testImport() throws Exception {
        String PATH = "H:\\LUA\\redis_springboot\\src\\main\\java\\com\\distributeredis\\redis_springboot\\springpoi\\明细表.xlsx";
        FileInputStream fis = new FileInputStream(PATH);
        // 获取一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        // 获取一个工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 获取第一行内容
        XSSFRow row = sheet.getRow(0);
        if(row != null) {
            // 获取所有列
            int numberOfCells = row.getPhysicalNumberOfCells();
            for (int cell = 0; cell < numberOfCells; cell++) {
                XSSFCell rowCell = row.getCell(cell);
                if(rowCell != null) {
                    String cellValue = rowCell.getStringCellValue();
                    System.out.print(cellValue + "| ");
                }
            }
        }
        System.out.println();
        // 获取表格内容
        int rows = sheet.getPhysicalNumberOfRows();
        // 从1开始，第一行是标题
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            XSSFRow currentRow = sheet.getRow(rowNum);
            if(currentRow != null) {
                // 获取当前行的列数
                int cells = currentRow.getPhysicalNumberOfCells();
                for (int col = 0; col < cells; col++) {
                    // 获取当前列的值
                    XSSFCell cell = currentRow.getCell(col);
                    CellType cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType) {
                        case STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN:  // 布尔类型
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK:  // 空
                            break;
                        case NUMERIC:  // 数值类型（日期，普通数值）
                            if(DateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            }else {
                                cell.setCellType(CellType.STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case ERROR:
                            break;
                    }
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
        workbook.close();
        fis.close();
    }
}
